from datetime import datetime
import pkg_resources
pkg_resources.require("SQLAlchemy>=0.3.10")
from turbogears.database import metadata, mapper
# import some basic SQLAlchemy classes for declaring the data model
# (see http://www.sqlalchemy.org/docs/04/ormtutorial.html)
from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.orm import relation
# import some datatypes for table columns from SQLAlchemy
# (see http://www.sqlalchemy.org/docs/04/types.html for more)
from sqlalchemy import String, Unicode, Integer, DateTime, Boolean, Float
from turbogears import identity

server_address = "127.0.0.1"
# PyHMon tables

nodes_table = Table('nodes', metadata,
        Column('node_id', Integer, primary_key = True),
        Column('name', Unicode(40), unique = True),
        Column('addr', Unicode(50)),
        Column('port', Integer),
        Column('proto', Integer),
        Column('status', Integer))

servers_table = Table('servers', metadata,
        Column('server_id', Integer, primary_key = True),
        Column('name', Unicode(30), unique = True),
        Column('addr', Unicode(50), unique = True),
        Column('port', Integer),
        Column('alertport', Integer),
        Column('proto', Integer),
        Column('status', Integer))

plugins_table = Table('plugins', metadata,
        Column('plugin_id', Integer, primary_key = True),
        Column('name', Unicode(30)),
        Column('node_id', Integer, ForeignKey("nodes.node_id")),
        Column('vtype', Integer), # value type: 0-int, 1-flt, 2-str
        Column('delay', Integer),
        Column('buffered', Integer),
        Column('snmp_oid', String(60)),
        Column('snmp_community', String(60)),
        Column('status', Boolean), # 0-active, 1-passive
        Column('lasttime', Integer),
        Column('title', Unicode(40)))

items_table = Table('items', metadata,
        Column('item_id', Integer, primary_key = True),
        Column('name', Unicode(30)),
        Column('stype', Integer),
        Column('label', Unicode(40)),
        Column('cdef', String(40)),
        Column('draw', String(10)),
        Column('alert_min', Integer),
        Column('alert_max', Integer),
        Column('plugin_id', Integer, ForeignKey("plugins.plugin_id")))

actions_table = Table('actions', metadata,
        Column('action_id', Integer, primary_key = True),
        Column('method', Unicode(20)),
        Column('group_id', Integer, ForeignKey("groups.group_id")),
        Column('event_type', Integer),
        Column('msg', Unicode(255)))

values_table = Table('value', metadata,
        Column('value_id', Integer, primary_key = True),
        Column('timestamp', Integer),
        Column('item_id', Integer, ForeignKey("items.item_id")),
        Column('value_int', Integer),
        Column('value_flt', Float),
        Column('value_str', String(255)))

events_table = Table('events', metadata,
        Column('event_id', Integer, primary_key = True),
        Column('event_type', Unicode(40)),
        Column('item_id', Integer, ForeignKey("items.item_id")),
        Column('action_id', Integer, ForeignKey("actions.action_id")),
        Column('lasttime', Integer))

node_server_table = Table('node_server', metadata,
            Column('node_id', Integer, ForeignKey('nodes.node_id',
                                                  onupdate='CASCADE',
                                                  ondelete = 'CASCADE')),
            Column('server_id', Integer, ForeignKey('servers.server_id',
                                                    onupdate='CASCADE',
                                                    ondelete = 'CASCADE')))

server_parents = Table('server_parents', metadata,
            Column('parent_id', Integer, ForeignKey('servers.server_id',
                                                  onupdate='CASCADE',
                                                  ondelete = 'CASCADE')),
            Column('child_id', Integer, ForeignKey('servers.server_id',
                                                  onupdate='CASCADE',
                                                  ondelete = 'CASCADE')))

# PyHMon classes

class Node(object):
    def by_id(cls, nodeid):
        return cls.query.filter_by(node_id = nodeid).first()
    by_id = classmethod(by_id)

class Server(object):
    def by_id(cls, serverid):
        return cls.query.filter_by(server_id = serverid).first()
    by_id = classmethod(by_id)
    
class Item(object):
    def by_id(cls, itemid):
        return cls.query.filter_by(item_id = itemid).first()
    by_id = classmethod(by_id)

class Plugin(object):
    def by_id(cls, plugid):
        return cls.query.filter_by(plugin_id = plugid).first()
    by_id = classmethod(by_id)

class Action(object):
    def by_id(cls, actionid):
        return cls.query.filter_by(action_id = actionid).first()
    by_id = classmethod(by_id)

class Value(object):
    def getValues(self, itemid, start, end, vtype):
        return
    
class Event(object):
    pass


# mappers between PyHMon tables and classes

mapper(Node, nodes_table, properties =
                   dict(plugins = relation(Plugin, backref = 'nodes')))

mapper(Server, servers_table, properties =
        dict(nodes = relation(Node, secondary = node_server_table,\
                              backref = 'servers'),
             parents = relation(Server, secondary = server_parents,
                            primaryjoin = server_parents.c.parent_id ==\
                                        servers_table.c.server_id,
                            secondaryjoin = server_parents.c.child_id ==\
                                        servers_table.c.server_id,
                            backref = 'children')))

mapper(Plugin, plugins_table, properties =
       dict(items = relation(Item, backref = 'plugin')))

mapper(Value, values_table)

mapper(Item, items_table, properties =
        dict(values  = relation(Value, backref = 'item'),
             events  = relation(Event, backref = 'item')))

mapper(Action, actions_table, properties =
       dict(events = relation(Event, backref = 'action')))
            
mapper(Event, events_table)



# Identity tables

visits_table = Table('visit', metadata,
    Column('visit_key', String(40), primary_key = True),
    Column('created', DateTime, nullable = False, default=datetime.now),
    Column('expiry', DateTime)
)

visit_identity_table = Table('visit_identity', metadata,
    Column('visit_key', String(40), primary_key = True),
    Column('user_id', Integer, ForeignKey('user.user_id'), index = True)
)

groups_table = Table('groups', metadata,
    Column('group_id', Integer, primary_key = True),
    Column('group_name', Unicode(16), unique = True),
    Column('display_name', Unicode(255)),
    Column('created', DateTime, default = datetime.now)
)

users_table = Table('user', metadata,
    Column('user_id', Integer, primary_key = True),
    Column('user_name', Unicode(16), unique = True),
    Column('email_address', Unicode(255), unique = True),
    Column('phone_num', Unicode(16), unique = True),
    Column('display_name', Unicode(255)),
    Column('password', Unicode(64)),
    Column('created', DateTime, default=datetime.now)
)

permissions_table = Table('permission', metadata,
    Column('permission_id', Integer, primary_key = True),
    Column('permission_name', Unicode(16), unique = True),
    Column('description', Unicode(255))
)

user_group_table = Table('user_group', metadata,
    Column('user_id', Integer, ForeignKey('user.user_id',
        onupdate='CASCADE', ondelete = 'CASCADE')),
    Column('group_id', Integer, ForeignKey('groups.group_id',
        onupdate='CASCADE', ondelete = 'CASCADE'))
)

group_permission_table = Table('group_permission', metadata,
    Column('group_id', Integer, ForeignKey('groups.group_id',
        onupdate='CASCADE', ondelete = 'CASCADE')),
    Column('permission_id', Integer, ForeignKey('permission.permission_id',
        onupdate='CASCADE', ondelete = 'CASCADE'))
)


# the identity model


class Visit(object):
    """
    A visit to your site
    """
    def lookup_visit(cls, visit_key):
        return cls.query.get(visit_key)
    lookup_visit = classmethod(lookup_visit)


class VisitIdentity(object):
    """
    A Visit that is link to a User object
    """
    pass


class Group(object):
    """
    An ultra-simple group definition.
    """
    def by_id(cls, groupid):
        return cls.query.filter_by(group_id = groupid).first()
    by_id = classmethod(by_id)


class User(object):
    """
    Reasonably basic User definition.
    Probably would want additional attributes.
    """

    def permissions(self):
        p = set()
        for g in self.groups:
            p |= set(g.permissions)
        return p
    permissions = property(permissions)

    def by_email_address(cls, email):
        """
        A class method that can be used to search users
        based on their email addresses since it is unique.
        """
        return cls.query.filter_by(email_address=email).first()
    by_email_address = classmethod(by_email_address)

    def by_user_name(cls, username):
        """
        A class method that permits to search users
        based on their user_name attribute.
        """
        return cls.query.filter_by(user_name=username).first()
    by_user_name = classmethod(by_user_name)
    
    def by_id(cls, userid):
        return cls.query.filter_by(user_id = userid).first()
    by_id = classmethod(by_id)

    def _set_password(self, password):
        """
        encrypts password on the fly using the encryption
        algo defined in the configuration
        """
        self._password = identity.encrypt_password(password)

    def _get_password(self):
        """
        returns password
        """
        return self._password

    password = property(_get_password, _set_password)


class Permission(object):
    """
    A relationship that determines what each Group can do
    """
    pass


# set up mappers between identity tables and classes

mapper(Visit, visits_table)

mapper(VisitIdentity, visit_identity_table,
        properties = dict(users = relation(User, backref = 'visit_identity')))

mapper(User, users_table,
        properties = dict(_password = users_table.c.password))

mapper(Group, groups_table, properties =
        dict(users = relation(User,secondary = user_group_table, backref = 'groups'),
            actions = relation(Action, backref = 'groups')))

mapper(Permission, permissions_table,
        properties = dict(groups = relation(Group,
                secondary = group_permission_table, backref = 'permissions')))

